package videostoreapp.dataobjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

//import javax.jms.Connection;
import javax.jms.ConnectionFactory;
import javax.jms.Destination;
import javax.jms.JMSException;
import javax.jms.MessageProducer;
import javax.jms.ObjectMessage;
import javax.jms.Queue;
import javax.jms.Session;
import javax.jms.TextMessage;
import javax.naming.InitialContext;

import videostoreapp.ApplicationException;
import videostoreapp.Catalogue;
import videostoreapp.Constants;
import videostoreapp.Customer;
//import videostoreapp.Delivery;
import videostoreapp.Order;
import videostoreapp.Catalogue.CatalogueInfo;
import videostoreapp.Customer.CustomerInfo;
import videostoreapp.OrderMgr;
//import videostoreapp.Delivery.DeliveryInfo;

public class OrderDO implements Order {

    Connection conn = null;
    PreparedStatement psNewOrd = null, psNewOrdLine = null,
            psUpdOrd = null, psUpdOrdStat = null,
            psDelOrd = null, psDelOrdLine = null,
            psGetOrd = null, psGetOrdLine = null,
            psUpdOrdStatWithDiD = null;

    public OrderDO() {
        try {
            conn = Constants.getConnection();

            psNewOrd = conn.prepareStatement(
                    "INSERT INTO Orders (customer_id, total, order_date, warehouse, store, status) VALUES (?, ?, ?, ?, ?, ?)",
                    Statement.RETURN_GENERATED_KEYS);
            psNewOrdLine = conn.prepareStatement(
                    "INSERT INTO OrderLine (order_id, id, catalogue_id, quantity) VALUES (?, ?, ?, ?)",
                    Statement.RETURN_GENERATED_KEYS);

            psUpdOrd = conn.prepareStatement(
                    "UPDATE Orders SET customer_id = ?, total = ?, order_date = ?, warehouse = ?, store = ?, status = ? WHERE ID = ?");

            psUpdOrdStat = conn.prepareStatement(
                    "UPDATE Orders SET status = ? WHERE ID = ?");

            psUpdOrdStatWithDiD = conn.prepareStatement(
                    "UPDATE Orders SET status = ?, delivery_id = ? WHERE ID = ?");

            psDelOrd = conn.prepareStatement(
                    "DELETE FROM Orders WHERE ID = ?");

            psDelOrdLine = conn.prepareStatement(
                    "DELETE FROM OrderLine WHERE order_id = ?");

            psGetOrd = conn.prepareStatement(
                    "SELECT customer_id, total, order_date, warehouse, store, status FROM Orders WHERE ID = ?");

            psGetOrdLine = conn.prepareStatement(
                    "SELECT id, catalogue_id, quantity FROM OrderLine WHERE order_id = ? ORDER BY id ASC");


        } catch (SQLException e) {
            Constants.SQLExceptionHandler(e);
        }
    }

    public float calculateOrderCost(OrderInfo order) {
        float orderCost = 0;
        Catalogue catalog = new CatalogueDO();
        for (OrderLineInfo oif : order.videos) {
            CatalogueInfo cat = catalog.getVideoById(oif.catalogue_id);
            orderCost += cat.price;
        }
        return orderCost;
    }

    public int createOrder(OrderInfo ord) throws ApplicationException {
        int ordId = -1;
        if (ord != null) {
            try {

                this.validateOrder(ord);

                ord.total = this.calculateOrderCost(ord);

                //(customer_id, total, order_date, warehouse, store, status)
                psNewOrd.setInt(1, ord.customerId);
                psNewOrd.setFloat(2, ord.total);
                psNewOrd.setTimestamp(3, ord.orderDate);
                psNewOrd.setString(4, ord.warehouse);
                psNewOrd.setString(5, ord.store);
                psNewOrd.setInt(6, ord.status);

                if (psNewOrd.executeUpdate() > 0) {
                    ResultSet rs = psNewOrd.getGeneratedKeys();
                    if (rs.next()) {
                        ordId = rs.getInt(1);
                    }
                    rs.close();

                    int i = 0;
                    for (OrderLineInfo line : ord.videos) {
                        //(order_id, id, catalogue_id, quantity)
                        try {
                            psNewOrdLine.setInt(1, ordId);
                            psNewOrdLine.setInt(2, i);
                            psNewOrdLine.setInt(3, line.catalogue_id);
                            psNewOrdLine.setInt(4, line.quantity);

                            psNewOrdLine.executeUpdate();

                            line.order_id = ordId;
                            line.id = i;
                            i++;
                        } catch (SQLException sqe) {
                            Constants.SQLExceptionHandler(sqe);
                        }
                    }
                }

            } catch (SQLException sqe) {
                Constants.SQLExceptionHandler(sqe);
            }

        }
        return ordId;
    }

    public void deleteOrder(int id) {
        try {
            psDelOrd.setInt(1, id);
            psDelOrd.executeUpdate();

            psDelOrdLine.setInt(1, id);
            psDelOrdLine.executeUpdate();

        } catch (SQLException sqe) {
            Constants.SQLExceptionHandler(sqe);
        }
    }

    public OrderInfo getOrderById(int id) {
        OrderInfo ordInf = null;

        try {
            psGetOrd.setInt(1, id);
            ResultSet rs = psGetOrd.executeQuery();
            if (rs.next()) {
                //(customer_id, total, order_date, warehouse, store, status)
                ordInf = new OrderInfo(
                        id,
                        rs.getInt("customer_id"),
                        rs.getFloat("total"),
                        rs.getTimestamp("order_date"),
                        rs.getString("warehouse"),
                        rs.getString("store"),
                        rs.getInt("status"));

                rs.close();

                ArrayList<OrderLineInfo> oifs = new ArrayList<OrderLineInfo>();

                psGetOrdLine.setInt(1, id);
                ResultSet rs2 = psGetOrdLine.executeQuery();
                while (rs2.next()) {
                    oifs.add(new OrderLineInfo(
                            id, //order_id
                            rs2.getInt("id"), //row_id
                            rs2.getInt("catalogue_id"),
                            rs2.getInt("quantity")));

                }
                rs2.close();

                ordInf.videos = oifs.toArray(new OrderLineInfo[oifs.size()]);
            }

        } catch (SQLException sqe) {
            Constants.SQLExceptionHandler(sqe);
        }

        return ordInf;
    }

    public void modifyOrder(OrderInfo order) throws ApplicationException {
        if (order != null) {
            validateOrder(order);
            order.total = this.calculateOrderCost(order);
            //(customer_id, total, order_date, warehouse, store, status)
            try {
                psUpdOrd.setInt(1, order.customerId);
                psUpdOrd.setFloat(2, order.total);
                psUpdOrd.setString(3, order.warehouse);
                psUpdOrd.setString(4, order.store);
                psUpdOrd.setInt(5, order.status);

                psDelOrdLine.setInt(1, order.id);

                psDelOrdLine.executeUpdate();
                psUpdOrd.executeUpdate();

                int i = 0;
                for (OrderLineInfo line : order.videos) {
                    //(order_id, id, catalogue_id, quantity)
                    try {
                        psNewOrdLine.setInt(1, order.id);
                        psNewOrdLine.setInt(2, i);
                        psNewOrdLine.setInt(3, line.catalogue_id);
                        psNewOrdLine.setInt(4, line.quantity);

                        psNewOrdLine.executeUpdate();

                        i++;
                    } catch (SQLException sqe) {
                        Constants.SQLExceptionHandler(sqe);
                    }
                }
            } catch (SQLException sqe) {
                Constants.SQLExceptionHandler(sqe);
            }
        }

    }

    public void submitOrder(int id) throws ApplicationException {

        try {

            OrderInfo oif = this.getOrderById(id);

            if (OrderInfo.OrderStatus.PENDING != oif.status) {
                throw new ApplicationException("Order already submitted");
            }

            //oif.id = 18;

            System.out.println("Order number is: " + oif.id);
            System.out.println("Customer number is: " + oif.customerId);
            System.out.println("Warehouse is: " + oif.warehouse);
            System.out.println("Order total is: " + oif.total);

            this.updateOrderStatus(id, Order.OrderInfo.OrderStatus.SUBMITTED);


            OrderMgr.getInstance().sendOrderMessage(oif);

        } catch (Exception ex) {
            Logger.getLogger(OrderDO.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void updateOrderStatus(int id, int newStatus) {

        try {
            psUpdOrdStat.setInt(1, newStatus);
            psUpdOrdStat.setInt(2, id);
            psUpdOrdStat.executeUpdate();
        } catch (SQLException e) {
            Constants.SQLExceptionHandler(e);
        }
    }

    public void validateOrder(OrderInfo ord) throws ApplicationException {
        Customer cust = new CustomerDO();
        Catalogue cat = new CatalogueDO();

        if (OrderInfo.OrderStatus.PENDING != ord.status) {
            throw new ApplicationException("Order already submitted");
        }

        CustomerInfo custInfo = cust.getCustomerById(ord.customerId);
        if (custInfo == null) {
            throw new ApplicationException("Customer not found");
        }

        CatalogueInfo catInfo = null;
        for (int i = 0; i < ord.videos.length; i++) {
            catInfo = cat.getVideoById(ord.videos[i].catalogue_id);
            if (catInfo == null) {
                throw new ApplicationException(
                        "Video #" + i + " not found in catalogue");
            }
        }

    }

    public void updateOrderStatusWithDeliveryId(int id, int newStatus, int dId) {

        try {
            psUpdOrdStatWithDiD.setInt(1, newStatus);
            psUpdOrdStatWithDiD.setInt(2, dId);
            psUpdOrdStatWithDiD.setInt(3, id);
            psUpdOrdStatWithDiD.executeUpdate();
        } catch (SQLException e) {
            Constants.SQLExceptionHandler(e);
        }
    }
}
